package com.jr.Dao.Impl;

import com.jr.Dao.ICouriersDao;
import com.jr.Pojo.Couriers;
import com.jr.Pojo.Packages;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CouriersDaoImpl implements ICouriersDao {
    DBHelper db = new DBHelper();
    @Override
    public int selectDataCount() {
        StringBuilder sql=new StringBuilder("select count(*) from couriers where 1=1");
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try {
            con=db.getCon();
            ps=con.prepareStatement(sql.toString());
            resultSet=ps.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(con,ps,resultSet);
        }
        return 0;
    }

    @Override
    public ArrayList<Couriers> selectAll(PageHelper ph) {
        StringBuilder sql=new StringBuilder("select * from couriers where 1=1");
        sql.append(" limit ?,?");
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();

        return db.dtQuery(sql.toString(), Couriers.class,index,ph.getShowNumber());
    }

    @Override
    public int insertCouriers(String name,String phone) {
        String sql ="insert into couriers value(null,?,?,0,0,default,default)";
        Object[] objs={name,phone};
        return db.dtUpdate(sql,objs);
    }

    @Override
    public int updateCouriers(Couriers couriers) {
        String sql=" update couriers set name=?, phone=?, current_packages=?, completed_packages=? where id=? ";
        Object[] objs= {couriers.getName(), couriers.getPhone(), couriers.getCurrent_packages(), couriers.getCompleted_packages(), couriers.getId()};
        return db.dtUpdate(sql,objs);
    }

    @Override
    public int delCouriers(Integer id) {
        String sql="delete from couriers where id=?";
        Object[] objs={id};
        return db.dtUpdate(sql,objs);
    }

    @Override
    public ArrayList<Couriers> selectByName(String name) {
        String sql="select * from couriers where name=?";
        Object[] objs={name};
        return db.dtQuery(sql,Couriers.class,objs);
    }

    @Override
    public int updateCurrent_packages(Packages packages) {
        String sql="update packages set status=? where id=?";
        Object[] objs={packages.getStatus(),packages.getId()};
        return db.dtUpdate(sql,objs);
    }

    @Override
    public int selectIdById(String id) {
        String sql="select courier_id from packages where id=?";
        return db.scalarQuery(sql,Integer.class,id);
    }

    public int selectCountCurrentByStatus(Integer id) {
        String sql = "select Count(*) from packages where (status='待发货' or status='运送中') and courier_id=?";
        return db.scalarQuery(sql, Integer.class,id);
    }

    // 查询已接收的包裹总数
    @Override
    public int selectCountCompleteByStatus(Integer id) {
        String sql = "select Count(*) from packages where status='已接收' and courier_id=?";
        // 假设存在查询单个值的方法
        return db.scalarQuery(sql, Integer.class,id);
    }

    @Override
    public int updateByName(Couriers couriers) {
        String sql="update couriers set current_packages=? , completed_packages=? where id=?";
        Object[] objs={couriers.getCurrent_packages(),couriers.getCompleted_packages(),couriers.getId()};
        return db.dtUpdate(sql,objs);
    }

    @Override
    public ArrayList<Couriers> selectAllDESC(PageHelper ph) {
        StringBuilder sql=new StringBuilder("select * from couriers where 1=1");
        sql.append(" ORDER BY completed_packages DESC");
        sql.append(" limit ?,?");
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();

        return db.dtQuery(sql.toString(), Couriers.class,index,ph.getShowNumber());
    }

    @Override
    public int selectAllDataCount(Integer courier_id) {
        StringBuilder sql=new StringBuilder("select count(*) from packages where courier_id=").append(courier_id);
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try {
            con=db.getCon();
            ps=con.prepareStatement(sql.toString());
            resultSet=ps.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(con,ps,resultSet);
        }
        return 0;
    }

    @Override
    public ArrayList<Packages> selectAll(Integer courier_id,  PageHelper ph) {
        StringBuilder sql=new StringBuilder("select * from packages where courier_id=").append(courier_id);
        sql.append(" limit ?,?");
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();

        return db.dtQuery(sql.toString(),Packages.class,index,ph.getShowNumber());
    }
}
